﻿using PPIMobileInterface.Data;
using PPIMobileInterface.Entities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Text;

namespace PPIMobileInterface.Logic
{
    /// <summary>
    /// A layer that performs logic operationsand links the application with the Data Layer.
    /// </summary>
    public static class LogicLayer
    {
        //Created by: VMCoder
        //Creation date: -
        //Last updated by: VMCoder
        //Last update: 2008.04.10

        #region Query
        /// <summary>
        /// Retrieves all full visits (with data, poles and images included) from the database
        /// </summary>
        public static List<EVisit> LoadAllCompleteVisits(string connString, bool allImages)
        {
            //Created by: VMCoder
            //Creation date: 2008.04.28
            //Last updated by: VMCoder
            //Last update: 2008.04.28

            List<EVisit> _Visits;
            EVisit _Visit;
            DataTable _Dt = new DataTable();
            DataTable _DtVisits = new DataTable();
            SqlCeConnection _Con = null;
            try
            {
                _Con = Utils.GetConnection(connString);
                _DtVisits = DataLayer.SelectAllVisits(_Con, allImages);
                _Visits = new List<EVisit>();
                foreach (DataRow _DrVisits in _DtVisits.Rows)
                {
                    #region Visit
                    _Visit = new EVisit(Utils.ToInt32(_DrVisits["VisitId"]), Utils.ToInt32(_DrVisits["VisitTypeFk"]), Utils.Todouble(_DrVisits["Latitude"]), Utils.Todouble(_DrVisits["Longitude"]), Utils.Todouble(_DrVisits["Altitude"]), Utils.ToInt32(_DrVisits["SatelliteCount"]), Utils.ToDateTime(_DrVisits["SatelliteTime"]), bool.Parse(_DrVisits["SolutionAvailable"].ToString()), Utils.ToInt32(_DrVisits["TopTypeFk"]));
                    _Visit.StructureServerID = Utils.ToInt32(_DrVisits["StructureServerID"]);
                    _Visit.UploadedDate = Utils.ToDateTime(_DrVisits["UploadedDate"]);
                    _Visit.ConnectionString = connString;
                    if (Utils.ToInt32(_DrVisits["Project"]) > 0)
                        _Visit.ProjectFk = Utils.ToInt32(_DrVisits["Project"]);
                    #endregion
                    #region Data
                    _Dt = DataLayer.SelectDataXVisit(_Visit.VisitId, _Con);
                    if (_Dt.Rows.Count > 0)
                    {
                        _Visit.Data = new EData(Utils.ToInt32(_Dt.Rows[0]["DataId"]), _Visit.VisitId);
                        #region Poles
                        _Dt = DataLayer.SelectPolesXData(_Visit.Data.DataId, _Con, allImages);
                        _Visit.Data.Poles = new EPole[_Dt.Rows.Count];
                        for (Int32 _I = 0; _I < _Dt.Rows.Count; _I++)
                        {
                            _Visit.Data.Poles[_I] = new EPole(Utils.ToInt32(_Dt.Rows[_I]["PoleId"]), _Visit.Data.DataId, Utils.ToInt32(_Dt.Rows[_I]["TopTypeFk"]), Utils.ToInt32(_Dt.Rows[_I]["SpeciesFk"]), Utils.ToInt32(_Dt.Rows[_I]["TreatmentFk"]), Utils.ToInt32(_Dt.Rows[_I]["Year"]), Utils.ToInt32(_Dt.Rows[_I]["ClassFk"]), Utils.Todouble(_Dt.Rows[_I]["Length"]), Utils.ToInt32(_Dt.Rows[_I]["LVisit"]), _Dt.Rows[_I]["LVisitor"].ToString(), _Dt.Rows[_I]["Number"].ToString(), Utils.Todouble(_Dt.Rows[_I]["Latitude"]), Utils.Todouble(_Dt.Rows[_I]["Longitude"]), Utils.Todouble(_Dt.Rows[_I]["Altitude"]), Utils.ToInt32(_Dt.Rows[_I]["SatelliteCount"]), Utils.ToDateTime(_Dt.Rows[_I]["SatelliteTime"]), bool.Parse(_Dt.Rows[_I]["SolutionAvailable"].ToString()));
                            _Visit.Data.Poles[_I].PoleServerID = Utils.ToInt32(_Dt.Rows[_I]["PoleServerID"]);
                            _Visit.Data.Poles[_I].UploadedDate = Utils.ToDateTime(_Dt.Rows[_I]["UploadedDate"]);
                        }
                        
                        #endregion
                    }
                    #endregion
                    #region Images
                    _Dt = DataLayer.SelectImagesXVisit(_Visit.VisitId, _Con, allImages);
                    _Visit.Images = new EImage[_Dt.Rows.Count];
                    for (Int32 _I = 0; _I < _Dt.Rows.Count; _I++)
                    {
                        _Visit.Images[_I] = new EImage(Utils.ToInt32(_Dt.Rows[_I]["ImageId"]), _Visit.VisitId, Utils.ToInt32(_Dt.Rows[_I]["PoleFk"]), true, Utils.Todouble(_Dt.Rows[_I]["Latitude"]), Utils.Todouble(_Dt.Rows[_I]["Longitude"]), Utils.Todouble(_Dt.Rows[_I]["Altitude"]), Utils.ToInt32(_Dt.Rows[_I]["SatelliteCount"]), Utils.ToDateTime(_Dt.Rows[_I]["SatelliteTime"]), bool.Parse(_Dt.Rows[_I]["SolutionAvailable"].ToString()), _Dt.Rows[_I]["Filename"].ToString());
                        _Visit.Images[_I].ImageServerID = Utils.ToInt32(_Dt.Rows[_I]["ImageServerID"]);
                        _Visit.Images[_I].PoleServerID = Utils.ToInt32(_Dt.Rows[_I]["PoleServerID"]);
                        _Visit.Images[_I].StructureServerID = Utils.ToInt32(_Dt.Rows[_I]["StructureServerID"]);
                        _Visit.Images[_I].UploadedDate = Utils.ToDateTime(_Dt.Rows[_I]["UploadedDate"]); 
                    }
                    #endregion
                    _Visits.Add(_Visit);
                }
                return _Visits;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static List<EVisit> LoadAllCompleteVisits(string connString)
        {
            return LoadAllCompleteVisits(connString, true);
        }

        /// <summary>
        /// Retrieves a full visit (with data, poles and images included) from the database
        /// </summary>
        public static EVisit LoadCompleteVisit(Int32 visitId)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            EVisit _Visit;
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            try
            {
                _Con = Utils.GetConnection();
                #region Visit
                _Dt = DataLayer.SelectVisit(visitId, _Con);
                if (_Dt.Rows.Count <= 0)
                    throw new Exception("Selected visit was not found on the database");
                _Visit = new EVisit(visitId, Utils.ToInt32(_Dt.Rows[0]["VisitTypeFk"]), Utils.Todouble(_Dt.Rows[0]["Latitude"]), Utils.Todouble(_Dt.Rows[0]["Longitude"]), Utils.Todouble(_Dt.Rows[0]["Altitude"]), Utils.ToInt32(_Dt.Rows[0]["SatelliteCount"]), Utils.ToDateTime(_Dt.Rows[0]["SatelliteTime"]), bool.Parse(_Dt.Rows[0]["SolutionAvailable"].ToString()), Utils.ToInt32(_Dt.Rows[0]["TopTypeFk"]));
                #endregion
                #region Data
                _Dt = DataLayer.SelectDataXVisit(visitId, _Con);
                if (_Dt.Rows.Count > 0)
                {
                    _Visit.Data = new EData(Utils.ToInt32(_Dt.Rows[0]["DataId"]), visitId);
                    #region Poles
                    _Dt = DataLayer.SelectPolesXData(_Visit.Data.DataId, _Con,true);
                    _Visit.Data.Poles = new EPole[_Dt.Rows.Count];
                    for (Int32 _I = 0; _I < _Dt.Rows.Count; _I++)
                        _Visit.Data.Poles[_I] = new EPole(Utils.ToInt32(_Dt.Rows[_I]["PoleId"]), _Visit.Data.DataId, Utils.ToInt32(_Dt.Rows[_I]["TopTypeFk"]), Utils.ToInt32(_Dt.Rows[_I]["SpeciesFk"]), Utils.ToInt32(_Dt.Rows[_I]["TreatmentFk"]), Utils.ToInt32(_Dt.Rows[_I]["Year"]), Utils.ToInt32(_Dt.Rows[_I]["ClassFk"]), Utils.Todouble(_Dt.Rows[_I]["Length"]), Utils.ToInt32(_Dt.Rows[_I]["LVisit"]), _Dt.Rows[_I]["LVisitor"].ToString(), _Dt.Rows[_I]["Number"].ToString(), Utils.Todouble(_Dt.Rows[_I]["Latitude"]), Utils.Todouble(_Dt.Rows[_I]["Longitude"]), Utils.Todouble(_Dt.Rows[_I]["Altitude"]), Utils.ToInt32(_Dt.Rows[_I]["SatelliteCount"]), Utils.ToDateTime(_Dt.Rows[_I]["SatelliteTime"]), bool.Parse(_Dt.Rows[_I]["SolutionAvailable"].ToString()));
                    #endregion
                }
                #endregion
                #region Images
                _Dt = DataLayer.SelectImagesXVisit(visitId, _Con, true);
                _Visit.Images = new EImage[_Dt.Rows.Count];
                for (Int32 _I = 0; _I < _Dt.Rows.Count; _I++)
                    _Visit.Images[_I] = new EImage(Utils.ToInt32(_Dt.Rows[_I]["ImageId"]), visitId, Utils.ToInt32(_Dt.Rows[_I]["PoleFk"]), true, Utils.Todouble(_Dt.Rows[_I]["Latitude"]), Utils.Todouble(_Dt.Rows[_I]["Longitude"]), Utils.Todouble(_Dt.Rows[_I]["Altitude"]), Utils.ToInt32(_Dt.Rows[_I]["SatelliteCount"]), Utils.ToDateTime(_Dt.Rows[_I]["SatelliteTime"]), bool.Parse(_Dt.Rows[_I]["SolutionAvailable"].ToString()), _Dt.Rows[_I]["Filename"].ToString());
                #endregion
                return _Visit;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static EVisit LoadCompleteVisit(Int32 visitId, string ConnectionString)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            EVisit _Visit;
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            try
            {
                _Con = Utils.GetConnection(ConnectionString);
                #region Visit
                _Dt = DataLayer.SelectVisit(visitId, _Con);
                if (_Dt.Rows.Count <= 0)
                    throw new Exception("Selected visit was not found on the database");
                _Visit = new EVisit(visitId, Utils.ToInt32(_Dt.Rows[0]["VisitTypeFk"]), Utils.Todouble(_Dt.Rows[0]["Latitude"]), Utils.Todouble(_Dt.Rows[0]["Longitude"]), Utils.Todouble(_Dt.Rows[0]["Altitude"]), Utils.ToInt32(_Dt.Rows[0]["SatelliteCount"]), Utils.ToDateTime(_Dt.Rows[0]["SatelliteTime"]), bool.Parse(_Dt.Rows[0]["SolutionAvailable"].ToString()), Utils.ToInt32(_Dt.Rows[0]["TopTypeFk"]));
                _Visit.ConnectionString = ConnectionString;
                if (Utils.ToInt32(_Dt.Rows[0]["Project"]) > 0)
                    _Visit.ProjectFk = Utils.ToInt32(_Dt.Rows[0]["Project"]);

                #endregion
                #region Data
                _Dt = DataLayer.SelectDataXVisit(visitId, _Con);
                if (_Dt.Rows.Count > 0)
                {
                    _Visit.Data = new EData(Utils.ToInt32(_Dt.Rows[0]["DataId"]), visitId);
                    #region Poles
                    _Dt = DataLayer.SelectPolesXData(_Visit.Data.DataId, _Con, true);
                    _Visit.Data.Poles = new EPole[_Dt.Rows.Count];
                    for (Int32 _I = 0; _I < _Dt.Rows.Count; _I++)
                        _Visit.Data.Poles[_I] = new EPole(Utils.ToInt32(_Dt.Rows[_I]["PoleId"]), _Visit.Data.DataId, Utils.ToInt32(_Dt.Rows[_I]["TopTypeFk"]), Utils.ToInt32(_Dt.Rows[_I]["SpeciesFk"]), Utils.ToInt32(_Dt.Rows[_I]["TreatmentFk"]), Utils.ToInt32(_Dt.Rows[_I]["Year"]), Utils.ToInt32(_Dt.Rows[_I]["ClassFk"]), Utils.Todouble(_Dt.Rows[_I]["Length"]), Utils.ToInt32(_Dt.Rows[_I]["LVisit"]), _Dt.Rows[_I]["LVisitor"].ToString(), _Dt.Rows[_I]["Number"].ToString(), Utils.Todouble(_Dt.Rows[_I]["Latitude"]), Utils.Todouble(_Dt.Rows[_I]["Longitude"]), Utils.Todouble(_Dt.Rows[_I]["Altitude"]), Utils.ToInt32(_Dt.Rows[_I]["SatelliteCount"]), Utils.ToDateTime(_Dt.Rows[_I]["SatelliteTime"]), bool.Parse(_Dt.Rows[_I]["SolutionAvailable"].ToString()));
                    #endregion
                }
                #endregion
                #region Images
                _Dt = DataLayer.SelectImagesXVisit(visitId, _Con, true);
                _Visit.Images = new EImage[_Dt.Rows.Count];
                for (Int32 _I = 0; _I < _Dt.Rows.Count; _I++)
                    _Visit.Images[_I] = new EImage(Utils.ToInt32(_Dt.Rows[_I]["ImageId"]), visitId, Utils.ToInt32(_Dt.Rows[_I]["PoleFk"]), true, Utils.Todouble(_Dt.Rows[_I]["Latitude"]), Utils.Todouble(_Dt.Rows[_I]["Longitude"]), Utils.Todouble(_Dt.Rows[_I]["Altitude"]), Utils.ToInt32(_Dt.Rows[_I]["SatelliteCount"]), Utils.ToDateTime(_Dt.Rows[_I]["SatelliteTime"]), bool.Parse(_Dt.Rows[_I]["SolutionAvailable"].ToString()), _Dt.Rows[_I]["Filename"].ToString());
                #endregion
                
                return _Visit;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        /// <summary>
        /// Retrieves data to fill the grid in the home page
        /// </summary>
        public static DataTable GetGridData()
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return DataLayer.GetGridData();
        }
        /// <summary>
        /// Select lookups of a particular type (usually for dropdown lists)
        /// </summary>
        public static DataTable SelectLookupsXType(string lookupType)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return DataLayer.SelectLookupsXType(lookupType);
        }
        /// <summary>
        /// Select tops for a particular visit type (usually for dropdown lists)
        /// </summary>
        public static DataTable SelectTopsXVisitType(Int32 visitTypeFk)
        {
            //Created by: VMCoder
            //Creation date: 2008.04.10
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            return DataLayer.SelectTopsXVisitType(visitTypeFk);
        }
        /// <summary>
        /// Retrieves application settings from database
        /// </summary>

        public static ESettingsStructure GetSettings(string connection)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            ESettingsStructure _Setttings = new ESettingsStructure();
            DataTable _Dt = DataLayer.GetSettings(connection);
            foreach (DataRow _Dr in _Dt.Rows)
            {
                switch (Int32.Parse(_Dr["SettingKey"].ToString()))
                {
                    case (Int32)Settings.Timezone:
                        _Setttings.Timezone = Int32.Parse(_Dr["SettingValue"].ToString());
                        break;
                    case (Int32)Settings.Project:
                        _Setttings.Project = new EProject(Int32.Parse(_Dr["SettingValue"].ToString()), _Dr["SettingName"].ToString());
                        break;
                    case (Int32)Settings.User:
                        _Setttings.Contact = new EUser(Int32.Parse(_Dr["SettingValue"].ToString()), _Dr["SettingName"].ToString());
                        break;
                    case (Int32)Settings.Utility:
                        _Setttings.Utility = new EUtility(Int32.Parse(_Dr["SettingValue"].ToString()), _Dr["SettingName"].ToString());
                        break;
                    case (Int32)Settings.Vendor:
                        _Setttings.Vendor = new EVendor(Int32.Parse(_Dr["SettingValue"].ToString()), _Dr["SettingName"].ToString());
                        break;
                    case (Int32)Settings.Handheld:
                        _Setttings.Handheld = new EHandheld(Int32.Parse(_Dr["SettingValue"].ToString()), _Dr["SettingName"].ToString());
                        break;
                    case (Int32)Settings.MobileVersion:
                        _Setttings.DatabaseVersion = _Dr["SettingName"].ToString();
                        break;
                }
            }
            return _Setttings;
        }

        public static List<EImage> SelectImages(string con,bool allImages)
        {
            #region Images
            SqlCeConnection _Con = null;
            try
            {
                _Con = Utils.GetConnection(con);
                DataTable  _Dt = DataLayer.SelectImages(_Con, allImages);
                List<EImage> _ImagesList = new List<EImage>();
                EImage _Image;

                for (Int32 _I = 0; _I < _Dt.Rows.Count; _I++)
                {
                    _Image = new EImage(Utils.ToInt32(_Dt.Rows[_I]["ImageId"]), 0, Utils.ToInt32(_Dt.Rows[_I]["PoleFk"]), true, Utils.Todouble(_Dt.Rows[_I]["Latitude"]), Utils.Todouble(_Dt.Rows[_I]["Longitude"]), Utils.Todouble(_Dt.Rows[_I]["Altitude"]), Utils.ToInt32(_Dt.Rows[_I]["SatelliteCount"]), Utils.ToDateTime(_Dt.Rows[_I]["SatelliteTime"]), bool.Parse(_Dt.Rows[_I]["SolutionAvailable"].ToString()), _Dt.Rows[_I]["Filename"].ToString());
                    _Image.ImageServerID = Utils.ToInt32(_Dt.Rows[_I]["ImageServerID"]);
                    _Image.PoleServerID = Utils.ToInt32(_Dt.Rows[_I]["PoleServerID"]);
                    _Image.StructureServerID = Utils.ToInt32(_Dt.Rows[_I]["StructureServerID"]);
                    _Image.UploadedDate = Utils.ToDateTime(_Dt.Rows[_I]["UploadedDate"]);
                    _ImagesList.Add(_Image);
                }
                return _ImagesList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
            #endregion
        }

        public static int GetTotalPoles(string connection)
        {
            return DataLayer.CountPoles(connection);
        }

        public static int GetTotalImages(string connection) 
        {
            return DataLayer.CountImages(connection);
        }

        public static int GetTotalAssets(string connection)
        {
            return DataLayer.CountVisits(connection);
        }

        public static List<EVisit> LoadAssets(string connection)
        {
            SqlCeConnection _Con = null;
            _Con = Utils.GetConnection(connection);
            DataTable _DtVisits = DataLayer.SelectAllVisits(_Con, true);
            EVisit _Visit;
            List<EVisit> _Visits = new List<EVisit>();
            foreach (DataRow _DrVisits in _DtVisits.Rows)
            {
                _Visit = new EVisit(Utils.ToInt32(_DrVisits["VisitId"]), Utils.ToInt32(_DrVisits["VisitTypeFk"]), Utils.Todouble(_DrVisits["Latitude"]), Utils.Todouble(_DrVisits["Longitude"]), Utils.Todouble(_DrVisits["Altitude"]), Utils.ToInt32(_DrVisits["SatelliteCount"]), Utils.ToDateTime(_DrVisits["SatelliteTime"]), bool.Parse(_DrVisits["SolutionAvailable"].ToString()), Utils.ToInt32(_DrVisits["TopTypeFk"]));
                _Visit.StructureServerID = Utils.ToInt32(_DrVisits["StructureServerID"]);
                _Visit.UploadedDate = Utils.ToDateTime(_DrVisits["UploadedDate"]);
                _Visit.ConnectionString = connection;
                _Visit.Images = new List<EImage>().ToArray();
                _Visit.Data = new EData();
                _Visit.Data.Poles = new List<EPole>().ToArray();
                _Visits.Add(_Visit);
            }
            return _Visits;
        }

        public static EProject SelectProjectById(int projectId, string connection)
        {
            DataRow dr = DataLayer.SelectProjectById(projectId, connection);
            if (dr == null)
                return new EProject();
            else
            {
                return new EProject(Utils.ToInt32(dr["ProjectId"]),dr["Name"].ToString());
            }
        }
        public static EUser SelectContactById(int contactId, string connection)
        {
            DataRow dr = DataLayer.SelectContactById(contactId, connection);
            if (dr == null)
                return new EUser();
            else
            {
                return new EUser(Utils.ToInt32(dr["UserId"]), dr["Name"].ToString());
            }
        }
        public static EVisitType SelectVisitById(int visitId, string connection)
        {
            DataRow dr = DataLayer.SelectVisitById(visitId, connection);
            if(dr== null)
                return new EVisitType();
            else
            {
                return new EVisitType(Utils.ToInt32(dr["LookupValue"]), dr["LookupText"].ToString());
            }
        }
        public static Int32 SelectDataIdByVisit(int visitId, string connection)
        {
            DataRow dr = DataLayer.SelectDataXVisit(visitId, Utils.GetConnection(connection)).Rows[0];
            return Utils.ToInt32(dr["DataId"]);
        }

        public static bool CheckNullablePositions(int visitId, string connection, ref string errorMessage)
        {
            bool _NotfoundedProblems = true;
            DataTable _dt = DataLayer.SearchNullVisitPosition(visitId, connection);
            StringBuilder _tmpErrorMessage = new StringBuilder();
            int Id = 0;
            foreach (DataRow dr in _dt.Rows)
            {
                Id = Int32.Parse(dr["VisitId"].ToString());
                _tmpErrorMessage.AppendLine("Latitude or Longitude is incomplete in Visit " + Id);
                _NotfoundedProblems = false;
            }
            int DataId = SelectDataIdByVisit(visitId,connection);
            _dt = DataLayer.SearchNullPolePosition(DataId, connection);
            foreach (DataRow dr in _dt.Rows)
            {
                Id = Int32.Parse(dr["PoleId"].ToString());
                _tmpErrorMessage.AppendLine("Latitude or Longitude is incomplete in Pole " + Id);
                _NotfoundedProblems = false;
            }
            errorMessage = _tmpErrorMessage.ToString();
            return _NotfoundedProblems;
        }


        #endregion

        #region CRUD
        /// <summary>
        /// Deletes a full visit (with data, poles and images included) from the database
        /// </summary>
        public static void DeleteCompleteVisit(Int32 visitId)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            SqlCeTransaction _Tr = null;
            SqlCeConnection _Con = null;
            try
            {
                _Con = Utils.GetConnection();
                _Tr = _Con.BeginTransaction();

                #region Images
                foreach (DataRow _Dr in DataLayer.SelectImagesFiles(visitId, _Con).Rows)
                    if (System.IO.File.Exists(PPIConstants.K_ImagesFolder + _Dr["Filename"]))
                        System.IO.File.Delete(PPIConstants.K_ImagesFolder + _Dr["Filename"]);
                DataLayer.DeleteImagesXVisit(visitId, _Tr);
                #endregion
                #region Poles
                Int32 _DataId = DataLayer.SelectDataId(visitId, _Tr);
                DataLayer.DeletePolesXData(_DataId, _Tr);
                #endregion
                #region Data
                DataLayer.DeleteDataXVisit(visitId, _Tr);
                #endregion
                #region Visit
                DataLayer.DeleteVisit(visitId, _Tr);
                #endregion

                _Tr.Commit();
            }
            catch (Exception _Ex)
            {
                if (_Tr != null)
                    _Tr.Rollback();
                throw _Ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static void DeleteCompleteVisit(Int32 visitId, string connectionString)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            SqlCeTransaction _Tr = null;
            SqlCeConnection _Con = null;
            try
            {
                _Con = Utils.GetConnection(connectionString);
                _Tr = _Con.BeginTransaction();

                #region Images
                foreach (DataRow _Dr in DataLayer.SelectImagesFiles(visitId, _Con).Rows)
                    if (System.IO.File.Exists(PPIConstants.K_ImagesFolder + _Dr["Filename"]))
                        System.IO.File.Delete(PPIConstants.K_ImagesFolder + _Dr["Filename"]);
                DataLayer.DeleteImagesXVisit(visitId, _Tr);
                #endregion
                #region Poles
                Int32 _DataId = DataLayer.SelectDataId(visitId, _Tr);
                DataLayer.DeletePolesXData(_DataId, _Tr);
                #endregion
                #region Data
                DataLayer.DeleteDataXVisit(visitId, _Tr);
                #endregion
                #region Visit
                DataLayer.DeleteVisit(visitId, _Tr);
                #endregion

                _Tr.Commit();
            }
            catch (Exception _Ex)
            {
                if (_Tr != null)
                    _Tr.Rollback();
                throw _Ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        /// <summary>
        /// Saves a full visit (with data, poles and images included) in the database
        /// </summary>
        public static void SaveCompleteVisit(EVisit visit)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            SqlCeTransaction _Tr = null;
            SqlCeConnection _Con = null;
            try
            {
                _Con = Utils.GetConnection();
                _Tr = _Con.BeginTransaction();

                if (visit.VisitId == Int32.MinValue)
                    visit.VisitId = DataLayer.InsertVisit(visit.VisitTypeFk, visit.Latitude, visit.Longitude, visit.Altitude, visit.SatelliteCount, visit.SatelliteTime, visit.SolutionAvailable, visit.TopTypeFk, _Tr);
                else
                    DataLayer.UpdateVisit(visit.VisitId, visit.VisitTypeFk, visit.Latitude, visit.Longitude, visit.Altitude, visit.SatelliteCount, visit.SatelliteTime, visit.SolutionAvailable, visit.TopTypeFk,visit.ProjectFk, _Tr);
                if (visit.Data == null)
                    visit.Data = new EData();
                if (visit.Data.DataId == Int32.MinValue)
                    visit.Data.DataId = DataLayer.InsertData(visit.VisitId, _Tr);
                else
                    DataLayer.UpdateData(visit.Data.DataId, visit.VisitId, _Tr);
                if (visit.Data.Poles != null)
                    foreach (EPole _Pole in visit.Data.Poles)
                        if (_Pole.PoleId == Int32.MinValue)
                            _Pole.PoleId = DataLayer.InsertPole(visit.Data.DataId, _Pole.TopTypeFk, _Pole.SpeciesFk, _Pole.TreatmentFk, _Pole.Year, _Pole.ClassFk, _Pole.LVisit, _Pole.LVisitor, _Pole.Number, _Pole.Length, _Pole.Latitude, _Pole.Longitude, _Pole.Altitude, _Pole.SatelliteCount, _Pole.SatelliteTime, _Pole.SolutionAvailable, _Tr);
                        else
                            DataLayer.UpdatePole(_Pole.PoleId, visit.Data.DataId, _Pole.TopTypeFk, _Pole.SpeciesFk, _Pole.TreatmentFk, _Pole.Year, _Pole.ClassFk, _Pole.LVisit, _Pole.LVisitor, _Pole.Number, _Pole.Length, _Pole.Latitude, _Pole.Longitude, _Pole.Altitude, _Pole.SatelliteCount, _Pole.SatelliteTime, _Pole.SolutionAvailable, _Tr);
                if (visit.Images != null)
                    for (Int32 _I = 0; _I < visit.Images.Length; _I++)
                    {
                        EImage _Image = visit.Images[_I];
                        if (_Image.PoleFk != Int32.MinValue && !_Image.PoleFkIsId)
                            _Image.PoleFk = visit.Data.Poles[_Image.PoleFk].PoleId;
                        if (_Image.ImageId == Int32.MinValue)
                        {
                            _Image.ImageId = DataLayer.InsertImage(visit.VisitId, _Image.PoleFk, _Image.Latitude, _Image.Longitude, _Image.Altitude, _Image.SatelliteCount, _Image.SatelliteTime, _Image.SolutionAvailable, _Image.Filename, _Tr);
                            System.IO.File.Move(PPIConstants.K_ImagesFolder + "temp_" + _I.ToString() + ".jpeg", PPIConstants.K_ImagesFolder + _Image.ImageId.ToString() + ".jpeg");
                            DataLayer.UpdateImageFilename(_Image.ImageId, _Image.ImageId.ToString() + ".jpeg", _Tr);
                        }
                        else
                            DataLayer.UpdateImage(_Image.ImageId, visit.VisitId, _Image.PoleFk, _Image.Latitude, _Image.Longitude, _Image.Altitude, _Image.SatelliteCount, _Image.SatelliteTime, _Image.SolutionAvailable, _Image.Filename, _Tr);
                    }
                if (visit.ImagesToDelete != null)
                    foreach (Int32 _ImageId in visit.ImagesToDelete)
                        DataLayer.DeleteImage(_ImageId, _Tr);
                if (visit.PolesToDelete != null)
                    foreach (Int32 _PoleId in visit.PolesToDelete)
                        DataLayer.DeletePole(_PoleId, _Tr);

                _Tr.Commit();
            }
            catch (Exception _Ex)
            {
                if (_Tr != null)
                    _Tr.Rollback();
                throw _Ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static void SaveCompleteVisit(EVisit visit, string connection)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            SqlCeTransaction _Tr = null;
            SqlCeConnection _Con = null;
            try
            {
                _Con = Utils.GetConnection(connection);
                _Tr = _Con.BeginTransaction();

                if (visit.VisitId == Int32.MinValue)
                    visit.VisitId = DataLayer.InsertVisit(visit.VisitTypeFk, visit.Latitude, visit.Longitude, visit.Altitude, visit.SatelliteCount, visit.SatelliteTime, visit.SolutionAvailable, visit.TopTypeFk, _Tr);
                else
                    DataLayer.UpdateVisit(visit.VisitId, visit.VisitTypeFk, visit.Latitude, visit.Longitude, visit.Altitude, visit.SatelliteCount, visit.SatelliteTime, visit.SolutionAvailable, visit.TopTypeFk,visit.ProjectFk, _Tr);
                if (visit.Data == null)
                    visit.Data = new EData();
                if (visit.Data.DataId == Int32.MinValue)
                    visit.Data.DataId = DataLayer.InsertData(visit.VisitId, _Tr);
                else
                    DataLayer.UpdateData(visit.Data.DataId, visit.VisitId, _Tr);
                if (visit.Data.Poles != null)
                    foreach (EPole _Pole in visit.Data.Poles)
                        if (_Pole.PoleId == Int32.MinValue)
                            _Pole.PoleId = DataLayer.InsertPole(visit.Data.DataId, _Pole.TopTypeFk, _Pole.SpeciesFk, _Pole.TreatmentFk, _Pole.Year, _Pole.ClassFk, _Pole.LVisit, _Pole.LVisitor, _Pole.Number, _Pole.Length, _Pole.Latitude, _Pole.Longitude, _Pole.Altitude, _Pole.SatelliteCount, _Pole.SatelliteTime, _Pole.SolutionAvailable, _Tr);
                        else
                            DataLayer.UpdatePole(_Pole.PoleId, visit.Data.DataId, _Pole.TopTypeFk, _Pole.SpeciesFk, _Pole.TreatmentFk, _Pole.Year, _Pole.ClassFk, _Pole.LVisit, _Pole.LVisitor, _Pole.Number, _Pole.Length, _Pole.Latitude, _Pole.Longitude, _Pole.Altitude, _Pole.SatelliteCount, _Pole.SatelliteTime, _Pole.SolutionAvailable, _Tr);
                if (visit.Images != null)
                    for (Int32 _I = 0; _I < visit.Images.Length; _I++)
                    {
                        EImage _Image = visit.Images[_I];
                        if (_Image.PoleFk != Int32.MinValue && !_Image.PoleFkIsId)
                            _Image.PoleFk = visit.Data.Poles[_Image.PoleFk].PoleId;
                        if (_Image.ImageId == Int32.MinValue)
                        {
                            _Image.ImageId = DataLayer.InsertImage(visit.VisitId, _Image.PoleFk, _Image.Latitude, _Image.Longitude, _Image.Altitude, _Image.SatelliteCount, _Image.SatelliteTime, _Image.SolutionAvailable, _Image.Name, _Tr);
                            System.IO.File.Copy(_Image.Filename, GetImagesDirectory(connection) + Path.DirectorySeparatorChar + _Image.ImageId.ToString() + ".jpeg",true);
                            DataLayer.UpdateImageFilename(_Image.ImageId, _Image.ImageId.ToString() + ".jpeg", _Tr);
                        }
                        else
                            DataLayer.UpdateImage(_Image.ImageId, visit.VisitId, _Image.PoleFk, _Image.Latitude, _Image.Longitude, _Image.Altitude, _Image.SatelliteCount, _Image.SatelliteTime, _Image.SolutionAvailable, _Image.Name, _Tr);
                    }
                if (visit.ImagesToDelete != null)
                    foreach (Int32 _ImageId in visit.ImagesToDelete)
                        DataLayer.DeleteImage(_ImageId, _Tr);
                if (visit.PolesToDelete != null)
                    foreach (Int32 _PoleId in visit.PolesToDelete)
                    {
                        foreach (EImage i in visit.Images)
                        {
                            if(i.PoleFk == _PoleId)
                                DataLayer.UpdateImage(i.ImageId, visit.VisitId, Int32.MinValue, i.Latitude, i.Longitude, i.Altitude, i.SatelliteCount, i.SatelliteTime, i.SolutionAvailable, i.Name, _Tr);
                        }
                        DataLayer.DeletePole(_PoleId, _Tr);
                    }
                        

                _Tr.Commit();
            }
            catch (Exception _Ex)
            {
                if (_Tr != null)
                    _Tr.Rollback();
                throw _Ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        /// <summary>
        /// Saves settings in the database
        /// </summary>
        public static void SaveSettings(ESettingsStructure settings)
        {
            //Created by: VMCoder
            //Creation date: -
            //Last updated by: VMCoder
            //Last update: 2008.04.10

            DataLayer.SaveSettings(settings.Timezone);
        }

        public static void InserSettings(ESettingsStructure settings, string connection)
        {
            SqlCeTransaction _Tr = null;
            SqlCeConnection _Con = null;
            try
            {
                _Con = Utils.GetConnection(connection);
                _Tr = _Con.BeginTransaction();

                DataLayer.SaveSettingsBase(settings.Project.Id, settings.Project.Name, settings.Project.Key, _Tr);
                DataLayer.SaveSettingsBase(settings.Contact.Id, settings.Contact.Name, settings.Contact.Key, _Tr);
                DataLayer.SaveSettingsBase(settings.Utility.Id, settings.Utility.Name, settings.Utility.Key, _Tr);
                DataLayer.SaveSettingsBase(settings.Vendor.Id, settings.Vendor.Name, settings.Vendor.Key, _Tr);
                DataLayer.SaveSettingsBase(1, settings.DatabaseVersion, "7", _Tr);
                //DataLayer.SaveSettingsBase(1, settings.MobileVersion.Name, settings.MobileVersion.Key, _Tr);
                DataLayer.SaveSettingsBase(settings.Handheld.Id, settings.Handheld.Name, settings.Handheld.Key, _Tr);
                _Tr.Commit();

            }
            catch (Exception ex)
            {
                if (_Tr != null)
                    _Tr.Rollback();
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }

        }

        public static void UpdateStructureServerID(Int32 structureSeverID, Int32 structureMobileId, string connectionString)
        {
            DataLayer.UpdateStructureServerID(structureSeverID, structureMobileId, connectionString);
        }
        public static void UpdatePoleServerID(Int32 poleServerID, Int32 poleMobileID, string connectionString)
        {
            DataLayer.UpdatePoleServerID(poleServerID, poleMobileID, connectionString);
        }
        public static void UpdateImageServerID(Int32 imageServerID, Int32 imageMobileID, string connectionString)
        {
            DataLayer.UpdateImageServerID(imageServerID, imageMobileID, connectionString);
        }


        public static string GetImagesDirectory(string connectionString)
        {
            string returnPath = "";
            if (connectionString != null)
            {
                DirectoryInfo _ConnectionDirectory = new DirectoryInfo(connectionString);
                DirectoryInfo[] _ImagesDirectory = _ConnectionDirectory.Parent.GetDirectories("Images");
                if (_ImagesDirectory.Length > 0)
                    returnPath = _ImagesDirectory[0].FullName;
            }
            return returnPath;

        }
        #endregion
    }
}
